package com.efficiency;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.CharUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.efficiency.dao.DataBaseMateDataDao;
import com.efficiency.entity.ColunmInfo;
import com.efficiency.entity.IndexInfo;
import com.efficiency.entity.TableInfo;
import lombok.Data;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

/**
 * @Description
 * @Author Vincent.jiao
 * @Date 2023/6/21 16:39
 */
public class ExcelDiffDemo {
    //过滤对比分析的表
    public List<String> excludeTable = Arrays.asList("loginlog");

    //数据库字典为位置
    public String excelPath = "D:\\wwwdev\\wizbank\\core\\main\\project\\core\\database\\core数据库详细设计文档.xls";

    //数据库配置
    public String dbUrl = "jdbc:mysql://192.168.4.108:3306/wiz20230625?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&allowMultiQueries=true&serverTimezone=UTC";
    public String dbName = "root";
    public String dbPass = "123456";

    //最终分析出的 html 页面保存位置
    public String htmlResultPath = "D:\\table.html";

    //不显示长度的字段类型
    List<String> notShowColunmSize = Arrays.asList("bigint", "ntext", "longtext", "bit"
            , "datetime", "int", "text");

    //开启测试模式就会缓存数据库元数据信息，以便快速测试
    public boolean isTest = true;

    public static void main(String[] args) throws SQLException, IOException {
        ExcelDiffDemo demo = new ExcelDiffDemo();
//        demo.analysisDiff();        //分析差异
        demo.analysisNew();        //生成全新
    }

    /**
     * 根据数据库结构，生成数据库字典.
     * @throws IOException
     * @throws SQLException
     */
    public void analysisNew() throws IOException, SQLException {
        Map<String, Table> excelMap = readExcel();
        Map<String, Table> dbMap = readDB();

        //添加
        Set<String> diffSet = new HashSet<>(dbMap.keySet());
        String html = "<h1>添加</h1>";
        int i = 0;
        for (String item : diffSet) {
            fusionInfo(excelMap.get(item), dbMap.get(item));
            html += getHtmlTable(dbMap.get(item));
        }

        saveResultHtml(html);
    }
    
    public void saveResultHtml(String html) throws IOException {
        //保存到文件
        File file = new File(htmlResultPath);
        if(!file.exists()) {
            file.createNewFile();
        }
        FileUtil.writeString(html, file, "UTF-8");
        System.out.println("OK");
    }

    /**
     * 对比旧的数据字典与数据库表结构分析出增量的数据.
     * 分析出增量的增加表、删除表、增加列、删除列信息 生成html，html 为table 格式，可以直接复制到 Excel
     * @throws IOException
     * @throws SQLException
     */
    public void analysisDiff() throws IOException, SQLException {
        Map<String, Table> excelMap = readExcel();
        Map<String, Table> dbMap = readDB();

        //添加
        Set<String> excelSet = excelMap.keySet();
        Set<String> dbSet = dbMap.keySet();

        Set<String> diffSet = new HashSet<>(dbSet);
        diffSet.removeAll(excelSet);
        String html = "<h1>添加</h1>";
        int i = 0;
        for (String item : diffSet) {
            html += getHtmlTable(dbMap.get(item));
        }

        //删除
        html += "<h1>删除 excel 的内容</h1>";
        html += "<h6>注意有些是表改名</h6>";
        diffSet = new HashSet<>(excelSet);
        diffSet.removeAll(dbSet);
        html += "<p>"+(StrUtil.join(",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;", diffSet))+"</p>";

        //修改
        html += "<h1>修改表</h1>";
        for (String name : excelMap.keySet()){
            if(dbMap.get(name) == null || excelMap.get(name) == null) {
                continue;
            }

            Map<String, Column> dbColMap = dbMap.get(name).getColumnList().stream().collect(
                    Collectors.toMap(Column::getName, e -> e, (key1, key2) -> key2));
            Map<String, Column> excelColMap = excelMap.get(name).getColumnList().stream().collect(
                    Collectors.toMap(Column::getName, e -> e, (key1, key2) -> key2));

            diffSet = new HashSet<>(dbColMap.keySet());
            diffSet.removeAll(excelColMap.keySet());
            if(CollUtil.isNotEmpty(diffSet)) {
                html += "<h5>"+name+"新增列</h5>";
                html += "<table>";
                for (String colName : diffSet) {
                    html += getHtmlColunm(dbColMap.get(colName)) ;
                }
                html += "</table>";
            }

            diffSet = new HashSet<>(excelColMap.keySet());
            diffSet.removeAll(dbColMap.keySet());
            if(CollUtil.isNotEmpty(diffSet)) {
                html += "<h5> "+name+" 删除列</h5>";
                html += "<p>"+(StrUtil.join(",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;", diffSet))+"</p>";
            }
        }

        saveResultHtml(html);
    }

    /**
     * 返回html表信息.
     * @param table
     * @return
     */
    public String getHtmlTable(Table table) {
        String html = "<table>";

        html += "<tr style=\"background-color: #ccc;\"> <td>表名</td> <td colspan=\"6\">" + table.getName() + "</td>" + "</tr>" ;
        html += "<tr> <td>描述</td> <td>" + (StrUtil.isNotEmpty(table.getDesc()) ? table.getDesc() : "") + "</td>" + "</tr>" ;
        html += "<tr>"
                    + "<td>列名</td>"
                    + "<td>类型</td>"
                    + "<td>允许空</td>"
                    + "<td>主外键</td>"
                    + "<td>索引</td>"
                    + "<td>默认值</td>"
                    + "<td>描述</td>"
                + "</tr>" ;

        for (Column column : table.getColumnList()) {
            html += getHtmlColunm(column) ;
        }

        html += "<table>";
        return html;
    }

    public String getHtmlColunm(Column column) {
        return "<tr>"
                + "<td>" + column.getName() + "</td>"
                + "<td>" + column.getType() + "</td>"
                + "<td>" + (column.isNotNull ? "not" : "") + "</td>"
                + "<td>" + (column.isAuto ? "主键" : StrUtil.isEmpty(column.keyRef) ? "" : column.keyRef ) + "</td>"
                + "<td>" + (StrUtil.isNotEmpty(column.idxName) ? column.idxName : "") + "</td>"
                + "<td>" + (StrUtil.isNotEmpty(column.defaultVal) ? column.defaultVal : "") + "</td>"
                + "<td>" + (StrUtil.isNotEmpty(column.commmon) ? column.commmon : "") + "</td>"
                + "</tr>";
    }

    public Map<String, Table> readDB() throws SQLException, IOException {
//        Connection connection = DriverManager.getConnection("jdbc:sqlserver://192.168.4.113:1433;databasename=wizbank74",
//                "sa", "cyber88**0");
        Connection connection = DriverManager.getConnection(dbUrl, dbName, dbPass);

        DataBaseMateDataDao dataDao = new DataBaseMateDataDao();

        List<TableInfo> tableInfoList = null;

        if(isTest) {
            File file = new File("D:\\db.json");
            if(!file.exists()) {
                file.createNewFile();
            }
            String content = FileUtil.readUtf8String(file);

            if(StrUtil.isNotEmpty(content)){
                tableInfoList = new JSONArray(content).toList(TableInfo.class);
            } else {
                tableInfoList = dataDao.getAllTableInfoDetail(connection, null);
                FileUtil.writeString(new JSONArray(tableInfoList).toString(),
                        file, "UTF-8");
            }
        } else {
            tableInfoList = dataDao.getAllTableInfoDetail(connection, null);
        }

        Map<String, TableInfo> tableInfoMap = new HashMap<>();
        for (TableInfo tableInfo : tableInfoList) {
            if(tableInfo.getTable_type().equalsIgnoreCase("table")){
                tableInfoMap.put(tableInfo.getTable_name(), tableInfo);
            }
        }

        //转换
        Map<String, Table> resultMap = new HashMap<>();
        for (String tableName : tableInfoMap.keySet()) {
            //跳过表
            if(excludeTable(tableName)) {
                continue;
            }

            TableInfo dbTableInfo = tableInfoMap.get(tableName);

            Table table = new Table();
            table.name = tableName;
            table.desc = dbTableInfo.getRemarks();
            table.columnList = new LinkedList<>();
            Map<String, String> idxMap = Collections.EMPTY_MAP;

            if(CollUtil.isNotEmpty(dbTableInfo.getIndexInfos())) {
                idxMap = dbTableInfo.getIndexInfos().stream().collect(
                        Collectors.toMap(IndexInfo::getColumn_name, e -> e.getIndex_name(), (key1, key2) -> key2));
            }

            for (ColunmInfo dbColunm : dbTableInfo.getColunmInfos()) {
                Column column = new Column();
                column.name = dbColunm.getColumn_name().trim().toLowerCase(Locale.ROOT);

                //处理字段长度
                if(StrUtil.isNotEmpty(dbColunm.getColumn_size())) {
                    dbColunm.setColumn_size(dbColunm.getColumn_size().replace("identity", ""));

                    //指定类型不加大小
                    if(notShowColunmSize.contains(dbColunm.getType_name().toLowerCase())) {
                        dbColunm.setColumn_size("");
                    }

                    //如果存在大小，并且不存在括号就自动加上
                    if(StrUtil.isNotEmpty(dbColunm.getColumn_size())
                            && !dbColunm.getColumn_size().startsWith("(")){
                        dbColunm.setColumn_size("("+dbColunm.getColumn_size()+")");
                    }
                }

                //翻译类型


                column.type = dbColunm.getType_name().toLowerCase() + dbColunm.getColumn_size();
                column.isNotNull = !StrUtil.equals(dbColunm.getIs_nullable(), "YES");
                column.commmon = dbColunm.getRemarks();
                column.isAuto = StrUtil.equals(dbColunm.getIs_autoincrement(), "YES");
                column.idxName = idxMap.get(column.name);
                column.defaultVal = dbColunm.getColumn_def();

                table.columnList.add(column);
            }

            resultMap.put(tableName, table);
        }

//        for (String name : tableInfoMap.keySet()){
//            System.out.println(name);
//        }

        return resultMap;
    }

    public Map<String, Table> readExcel(){
        ExcelReader excelReader = ExcelUtil.getReader(new File(excelPath), 0);
        Table table = null;
        Map<String, Table> allMap = new HashMap<>();

        for (int i = 0; i < excelReader.getRowCount(); i++) {
            List<Object> rowList = excelReader.readRow(i);
            if(CollUtil.isEmpty(rowList)) {
                continue;
            }

            if(rowList.get(0) == null) {
                continue;
            }

            String frise = rowList.get(0).toString().toLowerCase(Locale.ROOT).trim();

            if(frise.contains("表名")) {
                String tableName = rowList.get(1).toString().toLowerCase(Locale.ROOT).trim();
                //过滤表名（尾部过滤）
                for (int j = tableName.length() - 1; j >= 0; j--) {
                    char c = tableName.charAt(j);
                    if(!isNameChar(c)) {
                        tableName = tableName.substring(j == 0 ? 0 : j + 1);
                        break;
                    }
                }

                //跳过表
                if(excludeTable(tableName)) {
                    table = null;
                    continue;
                }

                table = allMap.get( tableName);
                if(table == null) {
                    table = new Table();
                    table.columnList = new LinkedList<>();
                    table.name = tableName;
                    allMap.put(tableName, table);
                }
            }

            if(table == null) {
                continue;
            }

            if(frise.contains("描述")) {
                table.desc = rowList.get(1) != null ? rowList.get(1).toString() : "";
            }

            //如果不是字段（字母开头），下一循环
            if(!(frise.charAt(0) >= 97 && frise.charAt(0) <= 122)) {
                continue;
            }

            if(rowList.size() <= 2) {
                //如果为列名，则不过滤
                if (!isColunmName(frise)) {
                    continue;
                }
            }

            Column column = new Column();
            Object objVal = rowList.get(0);
            column.name = objVal != null ? objVal.toString().trim().toLowerCase(Locale.ROOT) : "";

            objVal = rowList.get(1);
            column.type = objVal != null ? objVal.toString() : "";

            if(rowList.size() >= 3) {
                objVal = rowList.get(2);
                column.isNotNull = objVal != null && objVal.toString().contains("not");
            }

            if(rowList.size() >= 4) {
                objVal = rowList.get(3);
                column.keyRef = objVal != null ? objVal.toString() : "";
            }

            if(rowList.size() >= 5) {
                objVal = rowList.get(4);
                column.commmon = objVal != null ? objVal.toString() : "";
            }

            table.columnList.add(column);
        }

        return allMap;
    }

    /**
     * 融合 excel 与 数据库信息.
     * 因为数据库中很多列没有注释，但是旧的excel 中有，所以就融合下数据
     * @param excelTable
     * @param dbTable
     */
    public void fusionInfo(Table excelTable, Table dbTable) {
        if(excelTable == null || dbTable == null) {
            return;
        }

        if (StrUtil.isEmpty(dbTable.getDesc())) {
            dbTable.setDesc(excelTable.getDesc());
        }

        Map<String, Column> dbColMap = dbTable.getColumnList().stream().collect(
                Collectors.toMap(Column::getName, e -> e, (key1, key2) -> key2));
        Map<String, Column> excelColMap = excelTable.getColumnList().stream().collect(
                Collectors.toMap(Column::getName, e -> e, (key1, key2) -> key2));

        for(String colName : dbColMap.keySet()) {
            Column dbColumn = dbColMap.get(colName);
            Column excelColumn = excelColMap.get(colName);

            if(excelColumn == null || dbColumn == null) {
                continue;
            }

            if(StrUtil.isEmpty(dbColumn.keyRef)) {
                dbColumn.keyRef = excelColumn.keyRef;
            }
            if(StrUtil.isEmpty(dbColumn.commmon)) {
                dbColumn.commmon = excelColumn.commmon;
            }
        }
    }

    /**
     * 是否为合法的表名.
     * @param tableName
     * @return
     */
    public boolean isTableName(String tableName) {
        return isColunmName(tableName);
    }

    /**
     * 是否为合法的字段名
     * @param colunm
     * @return
     */
    public boolean isColunmName(String colunm) {
        for (int i = 0; i < colunm.length(); i++) {
            char c = colunm.charAt(i);
            if(!isNameChar(c)) {
                return false;
            }
        }

        return true;
    }

    /**
     * 是否可以作为名字的合法字符.
     * @param c
     * @return
     */
    public boolean isNameChar(char c) {
        return (c >= '0' && c <= '9')
                || (c >= 'a' && c <= 'z')
                || (c >= 'A' && c <= 'Z')
                || c == '_' ||  c == '-';
    }

    /**
     * 是否为排查的表.
     * @param name
     * @return
     */
    public boolean excludeTable(String name) {
        for (String table : excludeTable) {
            if(name.startsWith(table)) {
                return true;
            }
        }

        return false;
    }

    @Data
    class Table {
        public String name;
        public String desc;
        public List<Column> columnList;
    }

    @Data
    class Column{
        public boolean isAuto;  //是否自动增长
        public String name;
        public String type;
        public boolean isNotNull;
        public String keyRef;  //主外键
        public String commmon;  //描述
        public String idxName;   //是否为索引
        public String defaultVal;   //默认值
    }
}
